package registratiesysteem;

import java.sql.Driver;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Database {

    private static Driver driver;
    private String url;
    private Properties p = new Properties();
    private Connection c;
    private Statement s;
    private PreparedStatement ps;

    static {
        try {
            driver = new com.mysql.jdbc.Driver();
            System.out.println("MySQL driver loaded successfully.\n");
        } catch (Exception e) {
            throw new RuntimeException("Couldn't load SQL driver", e.getCause());
        }
    }

    public Database(String url) {
        this.url = url;
        p.setProperty("user", "readonly");
        p.setProperty("password", "readonly");
    }

    public Database(String url, String user, String password) {
        this.url = url;
        //user = "gebruiker1";
        //password = "gebruiker1";

        p.setProperty("user", user);
        p.setProperty("password", password);
        p.setProperty("user", user);
        p.setProperty("password", password);
    }

    public void setLogin(String user, String password) {
        p.setProperty("user", user);
        p.setProperty("password", password);
    }

    public void connect() {
        try {
            if (c == null) {
                c = driver.connect(url, p);
                s = c.createStatement();
                System.out.printf("Connected to the MySQL database as user %s\n", p.getProperty("user"));
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
            throw new RuntimeException("Fatal error, can not establish a connection to the MySQL server.");
        }
    }

    public void close() {
        try {
            if (c != null) {
                s.close();
                if (ps != null) {
                    ps.close();
                    ps = null;
                }
                c.close();
                c = null;
                System.out.printf("User %s has been disconnected from the MySQL server.\n", p.getProperty("user"));
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
    }

    public boolean hasResult() {
        return query() != null;
    }

    public boolean hasResult(String q) {
        if (q == null) {
            return false;
        }
        return query(q) != null;
    }

    public boolean IsUpdated() {
        return update() != -1;
    }

    public boolean IsUpdated(String q) {
        if (q == null) {
            return false;
        }
        return update(q) != -1;
    }

     private ArrayList getResults(ResultSet r) {
        ArrayList<ArrayList<String>> results = new ArrayList();
        int rows = 0;
        final int cols;

        if (r == null) {
            return results;
        }
        try {
            cols = r.getMetaData().getColumnCount();
            System.out.printf("Query column count: %d\n", cols);
            while (r.next()) {
                results.add(new ArrayList<String>());
                for (int i = 1; i <= cols; ++i) {
                    results.get(rows).add(r.getString(i));
                }
                ++rows;
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
        return results;
    }

    public void prepareStatement(String str) {
        try {
            if (ps != null) {
                ps.close();
                ps = null;
            }
            if (c != null) {
                ps = c.prepareStatement(str);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
    }
    public void statement(String str) {
        try {
            if (s != null) {
                s.close();
                s = null;
            }
            if (c != null) {
                s = c.prepareStatement(str);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
    }

    public void setString(int param, String value) {
        try {
            if (ps != null) {
                ps.setString(param, value);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
    }

    public void setFloat(int param, float value) {
        try {
            if (ps != null) {
                ps.setFloat(param, value);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
    }

    public void setInt(int param, int value) {
        try {
            if (ps != null) {
                ps.setInt(param, value);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
    }

    public void setShort(int param, short value) {
        try {
            if (ps != null) {
                ps.setShort(param, value);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
    }

    public ArrayList query() {
        try {
            if (ps != null) {
                return getResults(ps.executeQuery());
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
        return null;
    }

    public ArrayList query(String str) {
        try {
            if (s != null) {
                return getResults(s.executeQuery(str));
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
        return null;
    }


    public int rowCount(int onderdeelNummer){
        int i=0;
        try {
            s = c.createStatement();
            ResultSet r = s.executeQuery("SELECT COUNT(*) FROM  onderdeel WHERE idonderdeel = " + onderdeelNummer + " ;");
            while(r.next()){
                System.out.println("geteldte rijen onderdeel"+ r);
                return r.getInt(1);

            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return i;
    }
    public int rowCount(String Speelgoedcode){
        int i=0;
        try {
            s = c.createStatement();
            ResultSet r = s.executeQuery("SELECT COUNT(*) FROM  speelgoed WHERE code like \'" + Speelgoedcode + "\' ;");
            while(r.next()){
                return r.getInt(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return i;
    }


    public int update() {
        try {
            System.out.println(ps.toString());
            if (ps != null) {
                return ps.executeUpdate();
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
        return -1;
    }

    public int update(String str) {
        try {
            if (s != null) {
                return s.executeUpdate(str);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }
        return -1;
    }
}
